/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.inventario.dao;

import br.com.inventario.Inventario;
import br.com.inventario.util.FabricaConexoes;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author lucas
 */
public class InventarioDAO implements IInventarioDAO {

    private Connection conexao;

    public InventarioDAO() throws InventarioDAOException {
        try {
            this.conexao = FabricaConexoes.getConnection();
        } catch (Exception e) {
            throw new InventarioDAOException("Erro - " + e.getMessage());
        }
    }

    @Override
    public List<Inventario> select() throws InventarioDAOException {
        Connection conexaoLocal = null;
        PreparedStatement statementSelect = null;
        ResultSet rs = null;
        String SQL = "SELECT * FROM equipamento,categoria,fornecedor,localizacao "
                +"WHERE (categoria.cat_id = equipamento.categoria)AND(equipamento.fornecedor=fornecedor.forn_id)"
                +"AND(equipamento.localizacao=localizacao.loc_id) ORDER by numeroSerie";
        List<Inventario> listaInventario = new ArrayList<Inventario>();
        try {
            conexaoLocal = this.conexao;
            statementSelect = conexaoLocal.prepareStatement(SQL);
            rs = statementSelect.executeQuery();
            while (rs.next()) {
                Inventario objeto = new Inventario();

                objeto.setNumeroSerie(rs.getInt("numeroSerie"));
                objeto.setNome(rs.getString("nome"));
                objeto.setDescricao(rs.getString("descricao"));
                objeto.setFabricante(rs.getString("fabricante"));
                objeto.setMarca(rs.getString("marca"));
                objeto.setModelo(rs.getString("modelo"));
                objeto.setEndIp(rs.getString("enderecoIP"));
                objeto.setTipo(rs.getString("tipo"));
                objeto.setDtCompra(rs.getDate("dataCompra"));
                objeto.setValorCompra(rs.getDouble("valorCompra"));
                //objeto.setDtVenda(rs.getDate("dataVenda"));
                //objeto.setValorVenda(rs.getDouble("valorVenda"));
               // objeto.setValorDepreciacao(rs.getDouble("valorDepreciacao"));
                objeto.setCategoria(rs.getString("cat_tipo"));
                objeto.setFornecedor(rs.getString("forn_nome"));
                objeto.setLocalizacao(rs.getString("loc_local"));
                //objeto.setSoftware(rs.getString("soft_tipo"));
                listaInventario.add(objeto);
            }
        } catch (SQLException e) {
            System.out.println("Erro na consulta:" + e);
        } finally {
            FabricaConexoes.fecharConexao(conexaoLocal, statementSelect, rs);
        }
        return listaInventario;
    }

    @Override
    public void excluirAtivo(Inventario inventario) throws InventarioDAOException {
        PreparedStatement pstm = null;
        Connection conexaoLocal = null;
        String SQL = "DELETE FROM equipamento WHERE numeroSerie = ?";
        try {
            conexaoLocal = this.conexao;
            pstm = conexaoLocal.prepareStatement(SQL);
            pstm.setInt(1, inventario.getNumeroSerie());
            pstm.executeUpdate();
        } catch (SQLException e) {
            throw new InventarioDAOException("Erro ao excluir o item" + e);
        } finally {
            FabricaConexoes.fecharConexao(conexaoLocal, pstm);
        }
    }

    @Override
    public void gravarItem(Inventario inventario) throws InventarioDAOException {
        PreparedStatement insert = null;
        Connection conexaoLocal = null;
        String SQL = "INSERT INTO equipamento ( nome, descricao, "
                + " fabricante, marca, modelo, enderecoIP, tipo, dataCompra, "
                + " valorCompra, categoria, fornecedor, localizacao, software) "
                + " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            conexaoLocal = this.conexao;
            insert = conexaoLocal.prepareStatement(SQL);
            //insert.setInt(1, inventario.getNumeroSerie());
            insert.setString(1, inventario.getNome());
            insert.setString(2, inventario.getDescricao());
            insert.setString(3, inventario.getFabricante());
            insert.setString(4, inventario.getMarca());
            insert.setString(5, inventario.getModelo());
            insert.setString(6, inventario.getEndIp());
            insert.setString(7, inventario.getTipo());
            insert.setDate(8, new java.sql.Date(inventario.getDtCompra().getTime()));
            insert.setDouble(9, inventario.getValorCompra());
            insert.setString(10, inventario.getCategoria());
            insert.setString(11, inventario.getFornecedor());
            insert.setString(12, inventario.getLocalizacao());
            insert.setString(13, inventario.getSoftware());
            insert.executeUpdate();
        } catch (SQLException e) {
            throw new InventarioDAOException("Erro ao inclui o item" + e);
        } finally {
            FabricaConexoes.fecharConexao(conexaoLocal, insert);
        }
    }

  
        @Override
    public void alterarAtivo(Inventario inventario) throws InventarioDAOException {
        PreparedStatement pstm = null;
        Connection conexaoLocal = null;
        String SQL = "UPDATE equipamento SET nome = ?, descricao = ?, "
                + " fabricante = ?, marca = ?, modelo = ?, enderecoIp = ?, tipo = ?,"
                + " dataCompra = ?, valorCompra = ?, dataVenda = ?, valorVenda = ?, "
                + "valorDepreciacao = ?, categoria = ?, fornecedor = ?, localizacao = ?, software = ? "
                + "categoria = ?, fornecedor = ?, localizacao = ?, software = ?"
                + "WHERE numeroSerie = ?";
        try {
            conexaoLocal = this.conexao;
            pstm = conexaoLocal.prepareStatement(SQL);
            pstm.setString(1, inventario.getNome());
            pstm.setString(2, inventario.getDescricao());
            pstm.setString(3, inventario.getFabricante());
            pstm.setString(4, inventario.getMarca());
            pstm.setString(5, inventario.getModelo());
            pstm.setString(6, inventario.getEndIp());
            pstm.setString(7, inventario.getTipo());
            pstm.setDate(8, new java.sql.Date(inventario.getDtCompra().getTime()));
            pstm.setDouble(9, inventario.getValorCompra());
            pstm.setDate(10, new java.sql.Date(inventario.getDtVenda().getTime()));
            pstm.setDouble(11, inventario.getValorVenda());
            pstm.setDouble(12, inventario.getValorDepreciacao());
            pstm.setString(13, inventario.getCategoria());
            pstm.setString(14, inventario.getFornecedor());
            pstm.setString(15, inventario.getLocalizacao());
            pstm.setString(16, inventario.getSoftware());
            pstm.setInt(17, inventario.getNumeroSerie());

            pstm.executeUpdate();
        } catch (SQLException e) {
            throw new InventarioDAOException("Erro ao editar o item" + e);
        } finally {
            FabricaConexoes.fecharConexao(conexaoLocal, pstm);
        }
    }

    @Override
    public void venderAtivo(Inventario inventario) {
        throw new UnsupportedOperationException("Not supported yet.");
    }
}
